package cn.edu.sdjzu.cs.dingcan.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.sdjzu.cs.dingcan.entity.Food;
import cn.edu.sdjzu.cs.dingcan.entity.FoodType;
import cn.edu.sdjzu.cs.dingcan.utils.Condition;
import cn.edu.sdjzu.cs.dingcan.utils.JdbcUtils;
import cn.edu.sdjzu.cs.dingcan.utils.PageBean;

/**
 * 菜品数据访问类
 * 
 */
public class FoodDao {

	private final QueryRunner queryRunner;
	private final ResultSetHandler<Food> foodResultSetHandler;
	private final ResultSetHandler<List<Food>> listResultSetHandler;
	private final String sqlFindAll =
	 "SELECT f.id, f.food_name, f.food_type_id, f.price, f.mprice, f.remark, f.img, t.type_name AS food_type_name " + 
	 "  FROM food AS f LEFT JOIN foodtype AS t ON f.food_type_id = t.id ";

	public FoodDao() {
		this.queryRunner =  JdbcUtils.getQueryRunner();
		this.foodResultSetHandler = new ResultSetHandler<Food>() {
			@Override
			public Food handle(ResultSet rs) throws SQLException {
				if (!rs.next()) {
					return null;
				}
	
				Food food = new Food();
	
				food.setId(rs.getLong("id"));
				food.setName(rs.getString("food_name"));
				food.setPrice(rs.getBigDecimal("price"));
				food.setMprice(rs.getBigDecimal("mprice"));
				food.setRemark(rs.getString("remark"));
				food.setImg(rs.getString("img"));
				FoodType foodType = new FoodType();
				foodType.setId(rs.getLong("food_type_id"));
				foodType.setTypeName(rs.getString("food_type_name"));
				food.setFoodType(foodType);
	
				return food;
			}
		};
		this.listResultSetHandler = new ResultSetHandler<List<Food>>() {
			@Override
			public List<Food> handle(ResultSet rs) throws SQLException {
				List<Food> result = new ArrayList<>();
				Food food;
				do {
					food = foodResultSetHandler.handle(rs);
					if (food != null) {
						result.add(food);
					}
				} while (food != null);
				return result;
			}
		};
	}

	public void add(Food food) throws SQLException {
		String sql = "INSERT food(food_name, food_type_id, price, mprice, remark, img) VALUES(?, ?, ?, ? ,?, ?);";
		queryRunner.update(sql, food.getName(), food.getFoodType().getId(), food.getPrice(), food.getMprice(),
					food.getRemark(), food.getImg());
		
	}

	public void delete(int id) throws SQLException {
		String sql = "DELETE FROM food WHERE id = ?";
		queryRunner.update(sql, id);		
	}

	public void update(Food food) throws SQLException {
		// 如果food.img是null，则不更新该字段
		String sql = "UPDATE food " +
				     "   SET food_name = ?, food_type_id = ?, price = ?, mprice = ?, remark = ? " + (null == food.getImg() ? "" : ", img = ? ") +
				     " WHERE id = ?";
		if(null == food.getImg()) {
			queryRunner.update(sql, food.getName(), food.getFoodType().getId(), food.getPrice(), food.getMprice(),
					food.getRemark(), food.getId());
		} else {
			queryRunner.update(sql, food.getName(), food.getFoodType().getId(), food.getPrice(), food.getMprice(),
					food.getRemark(), food.getImg(), food.getId());
		}
	}

	public List<Food> getAll() throws SQLException {
		return queryRunner.query(sqlFindAll, listResultSetHandler);
	}

	public Food findById(Long id) throws SQLException {
		String sql = sqlFindAll + " WHERE f.id = ?";
		return queryRunner.query(sql, foodResultSetHandler, id);
	}

	public Food findByName(String name) throws SQLException {
		String sql = sqlFindAll + " WHERE f.food_name = ?";		
		return queryRunner.query(sql, foodResultSetHandler, name);
	}

	public List<Food> query(String keyword) throws SQLException {
		String sql = sqlFindAll + " WHERE f.food_name LIKE ?";
		return queryRunner.query(sql, listResultSetHandler, "%" + keyword + "%");
	}

	public List<Food> findByType(Long foodTypeId) throws SQLException {
		String sql = sqlFindAll + " WHERE f.food_type_id = ?";
		return queryRunner.query(sql, listResultSetHandler, foodTypeId);
	}
	
	public void getAll(PageBean<Food> pageBean) throws SQLException {
		int totalCount = this.getTotalCount(pageBean);
		pageBean.setTotalCount(totalCount);

		List<Object> list = new ArrayList<>();

		if (pageBean.getCurrentPage() <= 0) {
			pageBean.setCurrentPage(1); // 把当前页设置为1
		}
		if (pageBean.getCurrentPage() > pageBean.getTotalPage()) {
			// 把当前页设置为最大页数，同时避免将页数设为0（当无菜品数据时，getTotalPage()返回0
			pageBean.setCurrentPage(pageBean.getTotalPage() == 0 ? 1 : pageBean.getTotalPage());
		}

		// 获取当前页： 计算查询的起始行、返回的行数
		int currentPage = pageBean.getCurrentPage();
		int index = (currentPage - 1) * pageBean.getPageCount(); // 查询的起始行
		int count = pageBean.getPageCount(); // 查询返回的行数

		Condition condition = pageBean.getCondition();

		// 分页查询数据; 把查询到的数据设置到pageBean对象中
		StringBuilder sb = new StringBuilder();
		sb.append(sqlFindAll);
		sb.append(" WHERE   1=1 ");		

		// 判断
		if (condition != null) {
			String foodName = condition.getFoodName();
			if (foodName != null && !foodName.isEmpty()) {
				sb.append("  AND f.food_name LIKE ? ");
				list.add("%" + foodName + "%");
			}

			Long foodTypeId = condition.getFoodTypeId();
			if (foodTypeId != null) {
				sb.append(" AND f.food_type_id = ? ");
				list.add(foodTypeId);
			}
		}
		sb.append(" limit ?, ? ");
		list.add(index);
		list.add(count);
		
		// 根据当前页，查询当前页数据(一页数据)
		if (index >= 0) {
			List<Food> pageData = this.queryRunner.query(sb.toString(), this.listResultSetHandler, list.toArray());
			// 设置到pageBean对象中
			pageBean.setPageData(pageData);
		}
	}

	public int getTotalCount(PageBean<Food> pageBean) throws SQLException {
		StringBuilder sb = new StringBuilder();
		List<Object> list = new ArrayList<>();
		sb.append(" SELECT count(*) ");
		sb.append("   FROM food f LEFT JOIN foodtype t ON f.food_type_id = t.id");
		sb.append("  WHERE 1=1 ");

		Condition condition = pageBean.getCondition();
		// 判断
		if (condition != null) {
			String foodName = condition.getFoodName();
			if (foodName != null && !foodName.isEmpty()) {
				sb.append("  AND f.food_name LIKE ? ");
				list.add("%" + foodName + "%");
			}

			Long foodTypeId = condition.getFoodTypeId();
			if (foodTypeId != null) {
				sb.append(" AND f.food_type_id = ? ");
				list.add(foodTypeId);
			}
		}
		
		// 执行查询， 返回结果的第一行的第一列
		Long count = this.queryRunner.query(sb.toString(), new ScalarHandler<Long>(), list.toArray());
		return count.intValue();		
	}
}